package com.iplant.ams.serviceImpl.dao.dms.scada;

import com.iplant.ams.service.po.dms.DMSDeviceLedger;
import com.iplant.ams.service.po.dms.scada.alarm.DMSDeviceAlarm;
import com.iplant.ams.service.po.dms.scada.alarm.DMSDeviceHisAlarm;
import com.iplant.ams.service.po.dms.scada.status.DMSDeviceCurrentStatus;
import com.iplant.ams.service.po.dms.scada.status.DMSDeviceDetailStatus;
import com.iplant.ams.service.po.dms.scada.status.DMSDeviceHisStatus;
import com.iplant.ams.service.po.dms.scada.status.DMSDeviceStatus;
import com.iplant.ams.serviceImpl.dao.dms.DMSDeviceLedgerDAO;
import com.iplant.base.po.OutResult;
import com.iplant.base.po.Pagination;
import com.iplant.base.po.enums.DBEnumType;
import com.iplant.base.po.enums.DBSchemas;
import com.iplant.base.po.enums.ExceptionTypes;
import com.iplant.base.utils.general.CalendarTool;
import com.iplant.base.utils.general.StringUtils;
import com.iplant.base.utils.sql.BaseDAO;
import com.iplant.basic.service.po.bms.BMSEmployee;
import lombok.extern.slf4j.Slf4j;

import java.util.*;
import java.util.stream.Collectors;


@Slf4j
public class DMSDeviceStatusDAO extends BaseDAO {

    private static DMSDeviceStatusDAO Instance = null;

    private DMSDeviceStatusDAO() {
        super("dms_device_status", DBEnumType.MySQL, DBSchemas.AMS);
    }

    public static DMSDeviceStatusDAO getInstance() {
        if (Instance == null)
            Instance = new DMSDeviceStatusDAO();
        return Instance;
    }

    public static List<Integer> mStatusList = new ArrayList<Integer>();

    static {
        for (int i = 0; i <= 30; i++) {
            mStatusList.add(1 << i);
        }
    }

    private List<DMSDeviceStatus> DMS_SelectDeviceStatusList(BMSEmployee wLoginUser, List<Integer> wDeviceIDList, String wDeviceNo, String wDeviceName,
                                                             List<String> wAssetNoList, int wDeviceType, int wModelID, int wFactoryID, int wWorkShopID, int wAreaID,
                                                             int wActive, Calendar wStartTime, Calendar wEndTime, Pagination wPagination, OutResult<Integer> wErrorCode) {
        List<DMSDeviceStatus> wResult = new ArrayList<DMSDeviceStatus>();
        wErrorCode.set(0);
        try {
            if (wDeviceIDList == null)
                wDeviceIDList = new ArrayList<Integer>();
            wDeviceIDList.removeIf(p -> p <= 0);

            if (wAssetNoList == null)
                wAssetNoList = new ArrayList<String>();
            wAssetNoList.removeIf(p -> StringUtils.isEmpty(p));

            Calendar wBaseTime = CalendarTool.getBasicDate();
            if (wStartTime.compareTo(wBaseTime) < 0)
                wStartTime = wBaseTime;
            if (wEndTime.compareTo(wBaseTime) < 0)
                wEndTime = wBaseTime;
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResult;
            }
            List<DMSDeviceLedger> wDMSDeviceLedgerList = DMSDeviceLedgerDAO.getInstance().DMS_SelectDeviceLedgerList(wLoginUser, wDeviceIDList, wDeviceNo,
                    wDeviceName, wAssetNoList, wDeviceType, wModelID, wFactoryID, wWorkShopID, wAreaID, 1, wErrorCode);

            if (wErrorCode.Result != 0)
                return wResult;

            if (wDMSDeviceLedgerList == null || wDMSDeviceLedgerList.size() == 0)
                return wResult;

            Map<String, DMSDeviceLedger> wDeviceLedgerMap = wDMSDeviceLedgerList.stream().collect(Collectors.toMap(p -> p.AssetNo, p -> p, (o1, o2) -> o1));

            wAssetNoList = wDeviceLedgerMap.keySet().stream().collect(Collectors.toList());

            List<DMSDeviceHisStatus> wDeviceHisStatusList = DMSDeviceHisStatusDAO.getInstance().DMS_SelectDeviceHisStatusList(wLoginUser,
                    wAssetNoList, wActive, wStartTime, wEndTime, wPagination, wErrorCode);
            if (wErrorCode.Result != 0)
                return wResult;

            if (wDeviceHisStatusList == null || wDeviceHisStatusList.size() == 0) {
                return wResult;
            }
            DMSDeviceStatus wDeviceStatus;
            // wReader\[\"(\w+)\"\]
            for (DMSDeviceHisStatus wDeviceHisStatus : wDeviceHisStatusList) {
                if (wDeviceLedgerMap.containsKey(wDeviceHisStatus.AssetNo)) {
                    wDeviceStatus = wDeviceHisStatus.CreateDeviceStatus(wDeviceLedgerMap.get(wDeviceHisStatus.AssetNo));
                } else {
                    wDeviceStatus = wDeviceHisStatus.CreateDeviceStatus(null);
                }
                wResult.add(wDeviceStatus);
            }


        } catch (Exception e) {

            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("DMS_SelectDeviceStatusList", e);
        }
        return wResult;
    }


    private List<DMSDeviceStatus> DMS_SelectDeviceStatusDetailList(BMSEmployee wLoginUser, List<Integer> wDeviceIDList, String wDeviceNo, String wDeviceName,
                                                                   List<String> wAssetNoList, int wDeviceType, int wModelID, int wFactoryID, int wWorkShopID, int wAreaID, List<Integer> wStatusList,
                                                                   Calendar wStartTime, Calendar wEndTime, int wActive, Pagination wPagination, OutResult<Integer> wErrorCode) {
        List<DMSDeviceStatus> wResult = new ArrayList<DMSDeviceStatus>();
        try {

            wErrorCode.set(0);
            if (wDeviceIDList == null)
                wDeviceIDList = new ArrayList<Integer>();
            wDeviceIDList.removeIf(p -> p <= 0);

            if (wAssetNoList == null)
                wAssetNoList = new ArrayList<String>();
            wAssetNoList.removeIf(p -> StringUtils.isEmpty(p));

            if (wStatusList == null)
                wStatusList = new ArrayList<Integer>();
            wStatusList.removeIf(p -> p < 0);

            Calendar wBaseTime = CalendarTool.getBasicDate();
            if (wStartTime.compareTo(wBaseTime) < 0)
                wStartTime = wBaseTime;
            if (wEndTime.compareTo(wBaseTime) < 0)
                wEndTime = wBaseTime;
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResult;
            }
            List<DMSDeviceLedger> wDMSDeviceLedgerList = DMSDeviceLedgerDAO.getInstance().DMS_SelectDeviceLedgerList(wLoginUser, wDeviceIDList, wDeviceNo,
                    wDeviceName, wAssetNoList, wDeviceType, wModelID, wFactoryID, wWorkShopID, wAreaID, 1, wErrorCode);

            if (wErrorCode.Result != 0)
                return wResult;

            if (wDMSDeviceLedgerList == null || wDMSDeviceLedgerList.size() == 0)
                return wResult;

            Map<String, DMSDeviceLedger> wDeviceLedgerMap = wDMSDeviceLedgerList.stream().collect(Collectors.toMap(p -> p.AssetNo, p -> p, (o1, o2) -> o1));

            wAssetNoList = wDeviceLedgerMap.keySet().stream().collect(Collectors.toList());


            List<DMSDeviceDetailStatus> wDeviceDetailStatusList = DMSDeviceDetailStatusDAO.getInstance().DMS_SelectDeviceDetailStatusList(wLoginUser,
                    wAssetNoList, wStatusList, wStartTime, wEndTime, wActive, wPagination, wErrorCode);
            if (wErrorCode.Result != 0)
                return wResult;

            if (wDeviceDetailStatusList == null || wDeviceDetailStatusList.size() == 0) {
                return wResult;
            }
            DMSDeviceStatus wDeviceStatus;
            // wReader\[\"(\w+)\"\]
            for (DMSDeviceDetailStatus wDeviceDetailStatus : wDeviceDetailStatusList) {
                if (wDeviceLedgerMap.containsKey(wDeviceDetailStatus.AssetNo)) {
                    wDeviceStatus = wDeviceDetailStatus.CreateDeviceStatus(wDeviceLedgerMap.get(wDeviceDetailStatus.AssetNo));
                } else {
                    wDeviceStatus = wDeviceDetailStatus.CreateDeviceStatus(null);
                }
                wResult.add(wDeviceStatus);
            }
        } catch (Exception e) {

            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("DMS_SelectDeviceStatusDetailList", e);
        }
        return wResult;
    }


    public List<DMSDeviceStatus> DMS_CurrentDeviceStatusList(BMSEmployee wLoginUser, List<Integer> wDeviceIDList, String wDeviceNo, String wDeviceName,
                                                             List<String> wAssetNoList, int wDeviceType, int wModelID, int wFactoryID, int wWorkShopID,
                                                             int wAreaID, int wStatus, Calendar wStartTime, Calendar wEndTime, Pagination wPagination,
                                                             OutResult<Integer> wErrorCode) {
        List<DMSDeviceStatus> wResult = new ArrayList<DMSDeviceStatus>();
        try {

            wErrorCode.set(0);
            if (wDeviceIDList == null)
                wDeviceIDList = new ArrayList<Integer>();
            wDeviceIDList.removeIf(p -> p <= 0);

            if (wAssetNoList == null)
                wAssetNoList = new ArrayList<String>();
            wAssetNoList.removeIf(p -> StringUtils.isEmpty(p));

            Calendar wBaseTime = CalendarTool.getBasicDate();

            List<DMSDeviceLedger> wDMSDeviceLedgerList = DMSDeviceLedgerDAO.getInstance().DMS_SelectDeviceLedgerList(wLoginUser, wDeviceIDList, wDeviceNo,
                    wDeviceName, wAssetNoList, wDeviceType, wModelID, wFactoryID, wWorkShopID, wAreaID, 1, wErrorCode);

            if (wErrorCode.Result != 0)
                return wResult;

            if (wDMSDeviceLedgerList == null || wDMSDeviceLedgerList.size() == 0)
                return wResult;

            Map<String, DMSDeviceLedger> wDeviceLedgerMap = wDMSDeviceLedgerList.stream().collect(Collectors.toMap(p -> p.AssetNo, p -> p, (o1, o2) -> o1));

            wAssetNoList = wDeviceLedgerMap.keySet().stream().collect(Collectors.toList());


            List<DMSDeviceCurrentStatus> wDeviceCurrentStatusList = DMSDeviceCurrentStatusDAO.getInstance().DMS_SelectDeviceCurrentStatusList(wLoginUser,
                    wAssetNoList, wStatus, wStartTime, wEndTime, wPagination, wErrorCode);
            if (wErrorCode.Result != 0)
                return wResult;

            if (wDeviceCurrentStatusList == null || wDeviceCurrentStatusList.size() == 0) {
                return wResult;
            }
            DMSDeviceStatus wDeviceStatus;
            // wReader\[\"(\w+)\"\]
            for (DMSDeviceCurrentStatus wDeviceCurrentStatus : wDeviceCurrentStatusList) {
                if (wDeviceLedgerMap.containsKey(wDeviceCurrentStatus.AssetNo)) {
                    wDeviceStatus = wDeviceCurrentStatus.CreateDeviceStatus(wDeviceLedgerMap.get(wDeviceCurrentStatus.AssetNo));
                } else {
                    wDeviceStatus = wDeviceCurrentStatus.CreateDeviceStatus(null);
                }
                wResult.add(wDeviceStatus);
            }


        } catch (Exception e) {

            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("DMS_CurrentDeviceStatusList", e);
        }
        return wResult;
    }


    public List<DMSDeviceStatus> DMS_CurrentDeviceStatusList(BMSEmployee wLoginUser, List<Integer> wDeviceIDList, List<String> wAssetNoList, OutResult<Integer> wErrorCode) {
        return this.DMS_CurrentDeviceStatusList(wLoginUser, wDeviceIDList, "", "", wAssetNoList,
                -1, -1, -1, -1, -1, -1,
                CalendarTool.getBasicDate(), CalendarTool.getBasicDate(), Pagination.getMaxSize(DMSDeviceCurrentStatus.class), wErrorCode);
    }


    public DMSDeviceStatus DMS_CurrentDeviceStatus(BMSEmployee wLoginUser, int wDeviceID, String wDeviceNo, String wAssetNo,
                                                   OutResult<Integer> wErrorCode) {
        DMSDeviceStatus wResult = new DMSDeviceStatus();
        try {

            if (wDeviceID > 0 || StringUtils.isNotEmpty(wDeviceNo) || StringUtils.isNotEmpty(wAssetNo)) {
                List<DMSDeviceStatus> wDeviceStatusList = this.DMS_CurrentDeviceStatusList(wLoginUser, StringUtils.parseListArgs(wDeviceID), wDeviceNo, "",
                        StringUtils.parseListArgs(wAssetNo), -1, -1, -1, -1, -1, -1,
                        CalendarTool.getBasicDate(), CalendarTool.getBasicDate(), Pagination.getMaxSize(DMSDeviceCurrentStatus.class), wErrorCode);

                if (wDeviceStatusList != null && wDeviceStatusList.size() > 0) {
                    wResult = wDeviceStatusList.get(0);
                }
            }
        } catch (Exception e) {
            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("DMS_CurrentDeviceStatus", e);
        }
        return wResult;
    }


    public Map<Integer, Map<String, Integer>> DMS_SelectDeviceStatusTime(BMSEmployee wLoginUser, List<Integer> wDeviceIDList, String wDeviceNo, String wDeviceName,
                                                                         List<String> wAssetNoList, int wDeviceType, int wModelID, int wFactoryID, int wWorkShopID, int wAreaID,
                                                                         Calendar wStartTime, Calendar wEndTime, OutResult<Integer> wErrorCode) {
        Map<Integer, Map<String, Integer>> wResult = new HashMap<>();
        try {

            wErrorCode.set(0);
            if (wDeviceIDList == null)
                wDeviceIDList = new ArrayList<Integer>();
            wDeviceIDList.removeIf(p -> p <= 0);

            if (wAssetNoList == null)
                wAssetNoList = new ArrayList<String>();
            wAssetNoList.removeIf(p -> StringUtils.isEmpty(p));

            Calendar wBaseTime = CalendarTool.getBasicDate();
            if (wStartTime.compareTo(wBaseTime) < 0)
                wStartTime = wBaseTime;
            if (wEndTime.compareTo(wBaseTime) < 0)
                wEndTime = wBaseTime;
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResult;
            }

            List<DMSDeviceLedger> wDMSDeviceLedgerList = DMSDeviceLedgerDAO.getInstance().DMS_SelectDeviceLedgerList(wLoginUser, wDeviceIDList, wDeviceNo,
                    wDeviceName, wAssetNoList, wDeviceType, wModelID, wFactoryID, wWorkShopID, wAreaID, 1, wErrorCode);

            if (wErrorCode.Result != 0)
                return wResult;

            if (wDMSDeviceLedgerList == null || wDMSDeviceLedgerList.size() == 0)
                return wResult;

            Map<Integer, DMSDeviceLedger> wDeviceLedgerMap = wDMSDeviceLedgerList.stream().collect(Collectors.toMap(p -> p.ID, p -> p, (o1, o2) -> o1));

            wDeviceIDList = wDeviceLedgerMap.keySet().stream().collect(Collectors.toList());

            String wInstance = getDBName();


            StringBuilder wSB = new StringBuilder();
            String wColTemp = ", sum(case when p.DeviceStatus={0} then p.Duration else 0 end) as Status{0} ";
            for (int wStatus : mStatusList) {
                wSB.append(StringUtils.Format(wColTemp, wStatus));
            }


            String wSQL = StringUtils.Format(
                    " SELECT t.ID as DeviceIDL" + wSB.toString()
                            + " FROM {0}.dms_device_ledger t   "
                            + " left join {0}.dms_device_hisstatus p on t.AssetNo=p.DeviceID WHERE  1=1  "
                            + " and ( :wDeviceID ='''' or t.ID IN( ''{1}'' ) )  "
                            + " and ( :wStartTime <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or :wStartTime <= p.UpdateDate) "
                            + " and ( :wEndTime <= str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wEndTime >= p.StartDate) group by t.ID  ;"
                    , wInstance, StringUtils.Join("','", wDeviceIDList));
            wSQL = this.DMLChange(wSQL);
            Map<String, Object> wParamMap = new HashMap<String, Object>();
            wParamMap.put("wDeviceID", StringUtils.Join("','", wDeviceIDList));
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);

            List<Map<String, Object>> wQueryResult = this.queryForList(wSQL, wParamMap);

            if (wQueryResult.size() <= 0) {
                return wResult;
            }

            int wDeviceID = 0;
            // wReader\[\"(\w+)\"\]
            for (Map<String, Object> wReader : wQueryResult) {
                wDeviceID = StringUtils.parseInt(wReader.get("DeviceIDL"));
                if (!wResult.containsKey(wDeviceID))
                    wResult.put(wDeviceID, new HashMap<String, Integer>());

                for (int wStatus : mStatusList) {
                    wResult.get(wDeviceID).put(wStatus + "", StringUtils.parseInt(wReader.get("Status" + wStatus)));
                }
            }

        } catch (Exception e) {

            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("DMS_SelectDeviceStatusTime", e);
        }
        return wResult;
    }


    /**
     * 历史状态
     *
     * @param wLoginUser
     * @param wName
     * @param wAssetNo
     * @param wDeviceType
     * @param wModelID
     * @param wFactoryID
     * @param wWorkShopID
     * @param wAreaID
     * @param wActive
     * @param wStartTime
     * @param wEndTime
     * @param wPagination
     * @param wErrorCode
     * @return
     */
    public List<DMSDeviceStatus> DMS_SelectDeviceStatusList(BMSEmployee wLoginUser, String wName,
                                                            String wAssetNo, int wDeviceType, int wModelID, int wFactoryID, int wWorkShopID,
                                                            int wAreaID, int wActive, Calendar wStartTime, Calendar wEndTime, Pagination wPagination,
                                                            OutResult<Integer> wErrorCode) {
        List<DMSDeviceStatus> wResult = new ArrayList<DMSDeviceStatus>();
        try {
            wResult = DMS_SelectDeviceStatusList(wLoginUser, null, "", wName, StringUtils.parseListArgs(wAssetNo), wDeviceType,
                    wModelID, wFactoryID, wWorkShopID, wAreaID, wActive, wStartTime, wEndTime, wPagination,
                    wErrorCode);
        } catch (Exception e) {
            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("DMS_SelectDeviceStatusList", e);
        }
        return wResult;
    }

    public List<DMSDeviceStatus> DMS_SelectDeviceStatus(BMSEmployee wLoginUser, int wID, String wCode,
                                                        String wAssetNo, Calendar wStartTime, Calendar wEndTime, Pagination wPagination,
                                                        OutResult<Integer> wErrorCode) {
        List<DMSDeviceStatus> wResult = new ArrayList<DMSDeviceStatus>();
        try {
            if (wID > 0) {
                List<Integer> wIDList = new ArrayList<Integer>();
                wIDList.add(wID);
                wResult = this.DMS_SelectDeviceStatusDetailList(wLoginUser, wIDList, "", "",
                        null, -1, -1, -1, -1, -1, null,
                        wStartTime, wEndTime, -1, wPagination, wErrorCode);
                wResult.addAll(this.DMS_SelectDeviceStatusList(wLoginUser, wIDList, "", "",
                        null, -1, -1, -1, -1, -1, -1 - 1, wStartTime, wEndTime, wPagination, wErrorCode));
            } else if (StringUtils.isNotEmpty(wCode)) {
                wResult = this.DMS_SelectDeviceStatusList(wLoginUser, null, wCode, "",
                        null, -1, -1, -1, -1, -1, -1, wStartTime, wEndTime, wPagination, wErrorCode);
            } else if (StringUtils.isNotEmpty(wAssetNo)) {
                wResult = this.DMS_SelectDeviceStatusList(wLoginUser, null, "", wAssetNo,
                        null, -1, -1, -1, -1, -1, -1, wStartTime, wEndTime, wPagination, wErrorCode);
            }

        } catch (Exception e) {
            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("DMS_SelectDeviceStatus", e);
        }
        return wResult;
    }

    public List<DMSDeviceStatus> DMS_SelectDeviceStatusList(BMSEmployee wLoginUser, List<Integer> wIDList, Calendar wStartTime,
                                                            Calendar wEndTime, Pagination wPagination, OutResult<Integer> wErrorCode) {

        return this.DMS_SelectDeviceStatusList(wLoginUser, wIDList, "", "",
                null, -1, -1, -1, -1, -1,
                -1, wStartTime, wEndTime, wPagination, wErrorCode);
    }


    public Map<Integer, Map<String, Integer>> DMS_SelectDeviceStatusDetailTime(BMSEmployee wLoginUser, List<Integer> wID, String wCode, String wName,
                                                                               String wAssetNo, int wDeviceType, int wModelID, int wFactoryID, int wWorkShopID, int wLineID, int wAreaID, int wTeamID,
                                                                               Calendar wStartTime, Calendar wEndTime, OutResult<Integer> wErrorCode) {
        Map<Integer, Map<String, Integer>> wResult = new HashMap<Integer, Map<String, Integer>>();
        try {

            wErrorCode.set(0);
            String wInstance = getDBName();
            if (wErrorCode.Result != 0)
                return wResult;

            if (wID == null)
                wID = new ArrayList<Integer>();
            wID.removeIf(p -> p <= 0);
            if (wCode == null)
                wCode = "";


            Calendar wBaseTime = CalendarTool.getBasicDate();
            if (wStartTime.compareTo(wBaseTime) < 0)
                wStartTime = wBaseTime;
            if (wEndTime.compareTo(wBaseTime) < 0)
                wEndTime = wBaseTime;
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResult;
            }
            StringBuilder wSB = new StringBuilder();
            String wColTemp = ", sum(case when p.DeviceStatus={0} then p.Duration else 0 end) as Status{0} ";
            for (int wStatus : mStatusList) {
                wSB.append(StringUtils.Format(wColTemp, wStatus));
            }


            String wSQL = StringUtils.Format(
                    " SELECT t.ID as DeviceIDL" + wSB.toString()
                            + " FROM {0}.dms_device_ledger t inner join {0}.dms_device_model t1 on t.ModelID=t1.ID "
                            + " left join {0}.dms_device_detailstatus p on t.AssetNo=p.DeviceID WHERE  1=1  "
                            + " and ( :wID ='''' or t.ID IN( {1} ) )  "
                            + " and ( :wCode ='''' or t.Code  = :wCode)  "
                            + " and ( :wName ='''' or t.Name   = :wName )  "
                            + " and ( :wAssetNo ='''' or t.AssetNo   = :wAssetNo )  "
                            + " and ( :wDeviceType <= 0 or t1.DeviceType  = :wDeviceType)   "
                            + " and ( :wModelID <= 0 or t.ModelID  = :wModelID)   "
                            + " and ( :wAreaID <= 0 or t.AreaID  = :wAreaID)  "
                            + " and ( :wTeamID <= 0 or t.TeamID  = :wTeamID)   "
                            + " and ( :wFactoryID <= 0 or t.FactoryID  = :wFactoryID)  "
                            + " and ( :wWorkShopID <= 0 or t.WorkShopID  = :wWorkShopID)  "
                            + " and ( :wLineID <= 0 or t.LineID  = :wLineID)    "
                            + " and ( :wStartTime <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or p.UpdateDate <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or :wStartTime <= p.UpdateDate) "
                            + " and ( :wEndTime <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or p.StartDate <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or :wEndTime >= p.StartDate) group by t.ID  ;"
                    , wInstance, wID.size() > 0 ? StringUtils.Join(",", wID) : "0");
            wSQL = this.DMLChange(wSQL);
            Map<String, Object> wParamMap = new HashMap<String, Object>();
            wParamMap.put("wID", StringUtils.Join(",", wID));
            wParamMap.put("wName", wName);
            wParamMap.put("wCode", wCode);
            wParamMap.put("wAssetNo", wAssetNo);
            wParamMap.put("wDeviceType", wDeviceType);
            wParamMap.put("wModelID", wModelID);
            wParamMap.put("wTeamID", wTeamID);
            wParamMap.put("wFactoryID", wFactoryID);
            wParamMap.put("wWorkShopID", wWorkShopID);
            wParamMap.put("wLineID", wLineID);
            wParamMap.put("wAreaID", wAreaID);
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);

            List<Map<String, Object>> wQueryResult = this.queryForList(wSQL, wParamMap);

            if (wQueryResult.size() <= 0) {
                return wResult;
            }

            int wDeviceID = 0;
            // wReader\[\"(\w+)\"\]
            for (Map<String, Object> wReader : wQueryResult) {
                wDeviceID = StringUtils.parseInt(wReader.get("DeviceIDL"));
                if (!wResult.containsKey(wDeviceID))
                    wResult.put(wDeviceID, new HashMap<String, Integer>());

                for (int wStatus : mStatusList) {
                    wResult.get(wDeviceID).put(wStatus + "", StringUtils.parseInt(wReader.get("Status" + wStatus)));
                }
            }

        } catch (Exception e) {

            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("DMS_SelectDeviceStatusDetailTime", e);
        }
        return wResult;
    }


    public Map<Integer, Map<String, Integer>> DMS_SelectDeviceStatusHistoryTime(BMSEmployee wLoginUser, List<Integer> wDeviceIDList, String wDeviceNo, String wDeviceName,
                        List<String> wAssetNoList, int wDeviceType, int wModelID, int wFactoryID, int wWorkShopID, int wAreaID,
                                 Calendar wStartTime, Calendar wEndTime, OutResult<Integer> wErrorCode) {
        Map<Integer, Map<String, Integer>> wResult = new HashMap<Integer, Map<String, Integer>>();
        try {

            wErrorCode.set(0);
            String wInstance = getDBName();
            if (wErrorCode.Result != 0)
                return wResult;

            if (wDeviceIDList == null)
                wDeviceIDList = new ArrayList<Integer>();
            wDeviceIDList.removeIf(p -> p <= 0);

            if (wAssetNoList == null)
                wAssetNoList = new ArrayList<String>();
            wAssetNoList.removeIf(p -> StringUtils.isEmpty(p));

            if (wDeviceNo == null)
                wDeviceNo = "";


            Calendar wBaseTime = CalendarTool.getBasicDate();
            if (wStartTime.compareTo(wBaseTime) < 0)
                wStartTime = wBaseTime;
            if (wEndTime.compareTo(wBaseTime) < 0)
                wEndTime = wBaseTime;
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResult;
            }

            List<DMSDeviceLedger> wDMSDeviceLedgerList = DMSDeviceLedgerDAO.getInstance().DMS_SelectDeviceLedgerList(wLoginUser, wDeviceIDList, wDeviceNo,
                    wDeviceName, wAssetNoList, wDeviceType, wModelID, wFactoryID, wWorkShopID, wAreaID, 1, wErrorCode);

            if (wErrorCode.Result != 0)
                return wResult;

            if (wDMSDeviceLedgerList == null || wDMSDeviceLedgerList.size() == 0)
                return wResult;

            Map<Integer, DMSDeviceLedger> wDeviceLedgerMap = wDMSDeviceLedgerList.stream().collect(Collectors.toMap(p -> p.ID, p -> p, (o1, o2) -> o1));

            wDeviceIDList = wDeviceLedgerMap.keySet().stream().collect(Collectors.toList());


            String wSQL = StringUtils.Format(
                    " SELECT  t.ID AS DeviceID, t1.DeviceID as AssetNo,t1.DeviceStatus,  count(t1.DeviceID) as ItemCount," +
                            " sum(case when t1.Active=1 then  (unix_timestamp(now())-unix_timestamp(t1.StartDate))  else  t1.Duration end )  as Duration, t1.DeviceActStatus " +
                            " FROM {0}.dms_device_ledger t inner join {0}.dms_device_hisstatus t1 on t.AssetNo = t1.DeviceID   " +
                            " and ( :wID ='''' or t.ID IN( ''{1}'' ) )  " +
                            " and ( :wStartTime <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or t1.EndDate <= t1.StartDate or :wStartTime <= t1.EndDate) " +
                            " and ( :wEndTime <= str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wEndTime >= t1.StartDate)   group by DeviceID,  DeviceActStatus  ; "
                    , wInstance, StringUtils.Join("','", wDeviceIDList) );
            wSQL = this.DMLChange(wSQL);
            Map<String, Object> wParamMap = new HashMap<String, Object>();
            wParamMap.put("wID", StringUtils.Join("','", wDeviceIDList));
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);

            List<Map<String, Object>> wQueryResult = this.queryForList(wSQL, wParamMap);

            if (wQueryResult.size() == 0) {
                return wResult;
            }

            int wDeviceID = 0;
            int wStatus = 0;
            // wReader\[\"(\w+)\"\]
            for (Map<String, Object> wReader : wQueryResult) {
                wDeviceID = StringUtils.parseInt(wReader.get("DeviceID"));
                wStatus = StringUtils.parseInt(wReader.get("DeviceActStatus"));
                if (!wResult.containsKey(wDeviceID))
                    wResult.put(wDeviceID, new HashMap<String, Integer>());


                if (!wResult.get(wDeviceID).containsKey(wStatus + "")) {
                    wResult.get(wDeviceID).put(wStatus + "", StringUtils.parseInt(wReader.get("Duration")));
                } else {
                    wResult.get(wDeviceID).put(wStatus + "",
                            wResult.get(wDeviceID).get(wStatus + "") + StringUtils.parseInt(wReader.get("Duration")));
                }
            }

        } catch (Exception e) {

            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("DMS_SelectDeviceStatusHistoryTime", e);
        }
        return wResult;
    }


    public List<DMSDeviceStatus> DMS_SelectDeviceStatusDetailList(BMSEmployee wLoginUser, String wName, int wDeviceID,
                                                                  String wAssetNo, int wDeviceType, int wModelID, int wFactoryID, int wWorkShopID, int wAreaID,
                                                                  List<Integer> wStatus, Calendar wStartTime, Calendar wEndTime, Pagination wPagination,
                                                                  OutResult<Integer> wErrorCode) {
        List<DMSDeviceStatus> wResult = new ArrayList<DMSDeviceStatus>();
        try {
            wResult = DMS_SelectDeviceStatusDetailList(wLoginUser, StringUtils.parseListArgs(wDeviceID), "", wName, StringUtils.parseListArgs(wAssetNo), wDeviceType,
                    wModelID, wFactoryID, wWorkShopID, wAreaID, wStatus, wStartTime, wEndTime, -1, wPagination,
                    wErrorCode);
        } catch (Exception e) {
            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("DMS_SelectDeviceStatusDetailList", e);
        }
        return wResult;
    }

    public List<DMSDeviceStatus> DMS_SelectDeviceStatusDetail(BMSEmployee wLoginUser, int wID, String wCode,
                                                              String wAssetNo, List<Integer> wStatus, Calendar wStartTime, Calendar wEndTime, Pagination wPagination,
                                                              OutResult<Integer> wErrorCode) {

        if (wID > 0) {
            return this.DMS_SelectDeviceStatusDetailList(wLoginUser, StringUtils.parseListArgs(wID), "", "",
                    null, -1, -1, -1, -1, -1,
                    wStatus, wStartTime, wEndTime, -1, wPagination, wErrorCode);
        } else if (StringUtils.isNotEmpty(wCode)) {
            return this.DMS_SelectDeviceStatusDetailList(wLoginUser, null, wCode, "",
                    null, -1, -1, -1, -1, -1,
                    wStatus, wStartTime, wEndTime, -1, wPagination, wErrorCode);
        } else if (StringUtils.isNotEmpty(wAssetNo)) {
            return this.DMS_SelectDeviceStatusDetailList(wLoginUser, null, "", "",
                    StringUtils.parseListArgs(wAssetNo), -1, -1, -1, -1, -1,
                    wStatus, wStartTime, wEndTime, -1, wPagination, wErrorCode);
        }

        return new ArrayList<>();
    }

    public List<DMSDeviceStatus> DMS_SelectDeviceStatusDetailList(BMSEmployee wLoginUser, List<Integer> wIDList, List<Integer> wStatus, Calendar wStartTime, Calendar wEndTime, Pagination wPagination,
                                                                  OutResult<Integer> wErrorCode) {
        return this.DMS_SelectDeviceStatusDetailList(wLoginUser, wIDList, "", "", null, -1, -1, -1, -1, -1,
                wStatus, wStartTime, wEndTime, -1, wPagination, wErrorCode);
    }

    public List<DMSDeviceStatus> DMS_SelectDeviceCurrentStatusDetailList(BMSEmployee wLoginUser, List<Integer> wIDList,
                                                                         OutResult<Integer> wErrorCode) {
        return this.DMS_SelectDeviceStatusDetailList(wLoginUser, wIDList, "", "", null, -1, -1, -1, -1, -1,
                null, CalendarTool.getBasicDate(), CalendarTool.getBasicDate(), 1, Pagination.Create(1, 100000), wErrorCode);
    }

    //region 历史状态表操作

    public List<String> DMS_UpdateActiveStatusString(String wAssetNo, Calendar wStatusTime, int wStatus) {
        List<String> wResult = new ArrayList<String>();
        wResult.add(StringUtils.Format(" update {0}.dms_device_hisstatus set EndDate=str_to_date(''{1}'',''%Y-%m-%d %H:%i:%s'') ,UpdateDate=str_to_date(''{1}'',''%Y-%m-%d %H:%i:%s'') ," +
                        "Active=0,Duration=unix_timestamp(str_to_date(''{1}'',''%Y-%m-%d %H:%i:%s''))-unix_timestamp(StartDate) where DeviceID=''{2}'' AND DeviceStatus= {3} and Active=1;"
                ,
                getDBName(), StringUtils.parseCalendarToString(wStatusTime, "yyyy-MM-dd HH:mm:ss"), wAssetNo, wStatus));
        wResult.add(StringUtils.Format(
                " update {0}.dms_device_hisstatus set Active=0 where DeviceID=''{1}'' and Active=1;",
                getDBName(), wAssetNo));
        return wResult;
    }


    public String DMS_KeepActiveStatusString(DMSDeviceStatus wDMSDeviceStatus) {
        if (wDMSDeviceStatus == null || StringUtils.isEmpty(wDMSDeviceStatus.AssetNo) || wDMSDeviceStatus.StatusTimeEnd.get(Calendar.YEAR) <= 2010)
            return "";
        return StringUtils.Format(" update {0}.dms_device_hisstatus set EndDate=str_to_date(''{1}'',''%Y-%m-%d %H:%i:%s'') ,UpdateDate=str_to_date(''{1}'',''%Y-%m-%d %H:%i:%s'')," +
                        "Duration=unix_timestamp(str_to_date(''{1}'',''%Y-%m-%d %H:%i:%s''))-unix_timestamp(StartDate) where DeviceID=''{2}'' AND DeviceStatus= {3} and Active=1;"
                ,
                getDBName(), StringUtils.parseCalendarToString(wDMSDeviceStatus.StatusTimeEnd, "yyyy-MM-dd HH:mm:ss"), wDMSDeviceStatus.AssetNo, wDMSDeviceStatus.Status);
    }


    public String DMS_InsertActiveStatusString(DMSDeviceStatus wDMSDeviceStatus) {
        if (wDMSDeviceStatus == null || StringUtils.isEmpty(wDMSDeviceStatus.AssetNo) || wDMSDeviceStatus.StatusTime.get(Calendar.YEAR) <= 2010)
            return "";

        if (wDMSDeviceStatus.StatusTimeEnd.compareTo(wDMSDeviceStatus.StatusTime) <= 0)
            wDMSDeviceStatus.StatusTimeEnd = wDMSDeviceStatus.StatusTime;

        if (wDMSDeviceStatus.Duration <= 0) {
            wDMSDeviceStatus.Duration = (int) (CalendarTool.SubMilliseconds(wDMSDeviceStatus.StatusTimeEnd, wDMSDeviceStatus.StatusTime) / 1000);
        }

        return StringUtils.Format(" Insert into {0}.dms_device_hisstatus (DeviceID,DeviceStatus,StartDate,EndDate,Duration,UpdateDate,Active,DeviceActStatus) " +
                        "values ({1},{2},''{3}'',''{4}'',{5},''{4}'',1,{6});",
                getDBName(), wDMSDeviceStatus.AssetNo, wDMSDeviceStatus.Status, StringUtils.parseCalendarToString(wDMSDeviceStatus.StatusTime, "yyyy-MM-dd HH:mm:ss"),
                StringUtils.parseCalendarToString(wDMSDeviceStatus.StatusTimeEnd, "yyyy-MM-dd HH:mm:ss"), wDMSDeviceStatus.Duration, wDMSDeviceStatus.ActStatus);
    }

    //endregion

    public String DMS_UpdateCurrentStatusString(DMSDeviceStatus wDMSDeviceStatus) {
        if (wDMSDeviceStatus == null || StringUtils.isEmpty(wDMSDeviceStatus.AssetNo) || wDMSDeviceStatus.StatusTimeEnd.get(Calendar.YEAR) <= 2010)
            return "";

        if (wDMSDeviceStatus.ID > 0) {

            return StringUtils.Format(" Update  {0}.dms_device_status  set  UpdateDate =str_to_date(''{1}'',''%Y-%m-%d %H:%i:%s'') ," +
                            " ChangeDate=str_to_date(''{2}'',''%Y-%m-%d %H:%i:%s''),DeviceStatus={3},DeviceStatusL={4}" +
                            " where  DeviceID=''{5}'' ", getDBName(), StringUtils.parseCalendarToString(wDMSDeviceStatus.StatusTimeEnd, "yyyy-MM-dd HH:mm:ss"),
                    StringUtils.parseCalendarToString(wDMSDeviceStatus.StatusTime, "yyyy-MM-dd HH:mm:ss"), wDMSDeviceStatus.Status, wDMSDeviceStatus.StatusHistory, wDMSDeviceStatus.AssetNo);

        } else {
            return StringUtils.Format(" Insert into {0}.dms_device_status (DeviceID,DeviceStatus,ChangeDate,UpdateDate,DeviceStatusL) " +
                            "values ({1},{2},''{3}'',''{4}'',{5});", getDBName(), wDMSDeviceStatus.AssetNo, wDMSDeviceStatus.Status,
                    StringUtils.parseCalendarToString(wDMSDeviceStatus.StatusTime, "yyyy-MM-dd HH:mm:ss"), StringUtils.parseCalendarToString(wDMSDeviceStatus.StatusTimeEnd, "yyyy-MM-dd HH:mm:ss"),
                    wDMSDeviceStatus.StatusHistory);

        }
    }


    //region 历史详情表操作

    public List<String> DMS_CloseDetailStatusString(Collection<DMSDeviceStatus> wDMSDeviceStatusList, Calendar wStatusTime) {
        List<String> wResult = new ArrayList<String>();
        if (wDMSDeviceStatusList == null || wDMSDeviceStatusList.size() <= 0 || wStatusTime.get(Calendar.YEAR) <= 2010)
            return wResult;

        for (DMSDeviceStatus wDMSDeviceStatus : wDMSDeviceStatusList) {
            if (wDMSDeviceStatus.ID > 0) {
                wResult.add(StringUtils.Format(" update {0}.dms_device_detailstatus set UpdateDate=str_to_date(''{1}'',''%Y-%m-%d %H:%i:%s'') ," +
                                "Active=0,Duration=unix_timestamp(str_to_date(''{1}'',''%Y-%m-%d %H:%i:%s''))-unix_timestamp(StartDate) where DeviceID=''{2}'' AND DeviceStatus= {3} and Active=1;",
                        getDBName(), StringUtils.parseCalendarToString(wStatusTime, "yyyy-MM-dd HH:mm:ss"), wDMSDeviceStatus.AssetNo, wDMSDeviceStatus.Status));
            } else {
                wResult.add(StringUtils.Format(" INSERT INTO  {0}.dms_device_detailstatus (DeviceID,DeviceStatus,StartDate,UpdateDate,Duration,Active)   " +
                                "values ({1},{2},''{3}'',''{4}'',{5},0);",
                        getDBName(), wDMSDeviceStatus.AssetNo, wDMSDeviceStatus.Status, StringUtils.parseCalendarToString(wDMSDeviceStatus.StatusTime, "yyyy-MM-dd HH:mm:ss"),
                        StringUtils.parseCalendarToString(wStatusTime, "yyyy-MM-dd HH:mm:ss"), CalendarTool.SubDate(wStatusTime, wDMSDeviceStatus.StatusTime) / 1000));
            }
        }
        return wResult;
    }


    public List<String> DMS_KeepDetailStatusString(Collection<DMSDeviceStatus> wDMSDeviceStatusList, Calendar wStatusTime) {
        List<String> wResult = new ArrayList<String>();
        if (wDMSDeviceStatusList == null || wDMSDeviceStatusList.size() <= 0 || wStatusTime.get(Calendar.YEAR) <= 2010)
            return wResult;

        for (DMSDeviceStatus wDMSDeviceStatus : wDMSDeviceStatusList) {
            if (wDMSDeviceStatus.ID > 0) {
                wResult.add(StringUtils.Format(" update {0}.dms_device_detailstatus set UpdateDate=str_to_date(''{1}'',''%Y-%m-%d %H:%i:%s'') ," +
                                " Duration=unix_timestamp(str_to_date(''{1}'',''%Y-%m-%d %H:%i:%s''))-unix_timestamp(StartDate) where DeviceID=''{2}'' AND DeviceStatus= {3} and Active=1;",
                        getDBName(), StringUtils.parseCalendarToString(wStatusTime, "yyyy-MM-dd HH:mm:ss"), wDMSDeviceStatus.AssetNo, wDMSDeviceStatus.Status));
            } else {

                wResult.add(StringUtils.Format(" INSERT INTO  {0}.dms_device_detailstatus (DeviceID,DeviceStatus,StartDate,UpdateDate,Duration,Active)   " +
                                "values ({1},{2},''{3}'',''{4}'',{5},1);",
                        getDBName(), wDMSDeviceStatus.AssetNo, wDMSDeviceStatus.Status, StringUtils.parseCalendarToString(wDMSDeviceStatus.StatusTime, "yyyy-MM-dd HH:mm:ss"),
                        StringUtils.parseCalendarToString(wStatusTime, "yyyy-MM-dd HH:mm:ss"), CalendarTool.SubDate(wStatusTime, wDMSDeviceStatus.StatusTime) / 1000));
            }
        }
        return wResult;
    }


    //endregion


}
